package cn.mygweb.common.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Date;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import cn.mygweb.common.annotation.ExcelAnnotation;


/** 
* <p>Title: ExcelUtil.java</p>
* <p>Description: excel表格操作工具类</p>  
* @author gxb 
* @date 2019年8月13日 
* <p>Copyright: Copyright (c) 2019</p>
* <p>Company: www.mygweb.cn</p> 
*/
public class ExcelUtil {
	
	private static final String EXCEL_XLS = "xls";
	
	private static final String EXCEL_XLSX = "xlsx";
	
	public static final int EXPORT_07_LEAST_SIZE = 50000;//超过这个数量则使用xlsx

	/**
	 * <p>Title: getWorkbook</p>
	 * <p>Description: 判断excel的版本，获取Workbook对象</p>
	 * @param file
	 * @return
	 * @throws IOException
	 */
	public static Workbook getWorkbook(File file) throws IOException {
		Workbook workbook = null;
		FileInputStream in = new FileInputStream(file);
		if(file.getName().endsWith(EXCEL_XLS)) {
			workbook = new HSSFWorkbook(in);
		}else if(file.getName().endsWith(EXCEL_XLSX)) {
			workbook = new XSSFWorkbook(in);
		}
		return workbook;
	}
	
	@SuppressWarnings("rawtypes")
	public static List readExcel(File file) {
		try {
			InputStream is = new FileInputStream(file.getAbsolutePath());
			jxl.Workbook wb = jxl.Workbook.getWorkbook(is);
			
			List<List> finalList = new ArrayList<List>();
			
			int sheetSize = wb.getNumberOfSheets();
			//循环找sheet
			for(int index = 0; index < sheetSize; index++) {
				List<List> outerList = new ArrayList<List>();
				jxl.Sheet sheet = wb.getSheet(index);
				int rows = sheet.getRows();
				System.out.println("工作表sheet" + index + "总共有" + rows + "行！！！");
				//循环读取行
				for(int i = 0; i < sheet.getRows(); i++) {
					List<String> innerList = new ArrayList<String>();
					//循环读取列
					System.out.print("第" + i + "行===>");
					for(int j = 0; j < sheet.getColumns(); j++) {
						String cellinfo = sheet.getCell(j, i).getContents();
						if(cellinfo.isEmpty()) {
							continue;
						}
						innerList.add(cellinfo);
						System.out.print(cellinfo + "\t");
					}
					outerList.add(i, innerList);
					System.out.println();
				}
				finalList.add(index, outerList);
			}
			return finalList;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	@SuppressWarnings("rawtypes")
	public static List readExcel(File file, String nullMsg) {
		try {
			InputStream is = new FileInputStream(file.getAbsolutePath());
			Workbook wb = getWorkbook(file);
//			Workbook wb = new XSSFWorkbook(file);
			
			List<List> finalList = new ArrayList<List>();
			
			int sheetSize = wb.getNumberOfSheets();
			//循环找sheet
			for(int index = 0; index < sheetSize; index++) {
				List<List> outerList = new ArrayList<List>();
				Sheet sheet = wb.getSheetAt(index);
				int rows = sheet.getLastRowNum();
				System.out.println("工作表sheet" + index + "总共有" + rows + "行！！！");
				//循环读取行
				for(int i = 0; i < rows; i++) {
					Row row = sheet.getRow(i);
					List<String> innerList = new ArrayList<String>();
					//循环读取列
					System.out.print("第" + i + "行===>");
					if(row != null) {
						for(int j = 0; j < row.getLastCellNum(); j++) {
							Cell cell = row.getCell(j);
							if(cell != null) {
								String cellinfo = cell.toString();
								if(cellinfo.isEmpty()) {
									continue;
								}
								innerList.add(cellinfo);
								System.out.print(cellinfo + "\t");
							}
						}
						outerList.add(i, innerList);
						System.out.println();
					}
				}
				finalList.add(index, outerList);
			}
			return finalList;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * <p>Title: writeExcel</p>
	 * <p>Description: 创建Excel表格（xlsx）</p>
	 * @param list
	 * @param title
	 * @param className
	 * @param exportType
	 * @return
	 * @throws SecurityException 
	 * @throws NoSuchMethodException 
	 */
	public HSSFWorkbook writeExcel(List<T> list, String title, Class className, Integer exportType) throws NoSuchMethodException, SecurityException {
		// 获取属性
		Field[] fields = className.getDeclaredFields();
		List<Field> fieldList = new ArrayList<Field>();
		for (Field field : fields) {
			if(field.isAnnotationPresent(ExcelAnnotation.class)) {
				fieldList.add(field);
			}
		}
		// 按照id进行排序
		Collections.sort(fieldList, new Comparator<Field>() {
			@Override
			public int compare(Field f1, Field f2) {
				return f1.getAnnotation(ExcelAnnotation.class).id() - f2.getAnnotation(ExcelAnnotation.class).id();
			}
		});
		int columnSize = fieldList.size(),
				rowIndex = 0;
		// 创建一个HSSFWorkbook对象（Excel的文档对象）
		HSSFWorkbook hWorkbook = new HSSFWorkbook();
		// 创建一个HSSFSheet对象（excel的表单）
		HSSFSheet hSheet = hWorkbook.createSheet();
		// 创建行
		HSSFRow hRow = hSheet.createRow(rowIndex++);
		// 设置行高
		hRow.setHeight((short)380);
		// 创建单元格(从0开始)
		HSSFCell hCell = hRow.createCell(0);
		// 样式对象
		HSSFCellStyle cellStyle = getCellStyle(hWorkbook, (short)300, (short)500);
		// 将对象赋给单元格
		hCell.setCellStyle(cellStyle);
		// 设置行标题
		hCell.setCellValue(title);
		
		// 如果获取不到结果的话，则返回一个没有具体数据的表格
		if(getHuoResult(fieldList.isEmpty(), list == null, list.isEmpty())) {
			return hWorkbook;
		}
		
		// 创建第二行，此行为列名
		hRow = hSheet.createRow(rowIndex++);
		cellStyle = getCellStyle(hWorkbook, (short)270, (short)500);
		generateTitle(exportType, fieldList, columnSize, hSheet, hRow, cellStyle);
		
		// 创建所有的数据行
		cellStyle = getCellStyle(hWorkbook, (short)220, (short)500);
		generateData(list, fieldList, columnSize, rowIndex, hSheet, cellStyle);
		
		/*
	         * 第1个参数：从哪一行开始
	         * 第2个参数：到哪一行结束
	         * 第3个参数：从哪一列开始
	         * 第4个参数：到哪一列结束
         */
		hSheet.addMergedRegion(new CellRangeAddress(0, 0, 0, columnSize-1));
		
		// 固定表头（前一个参数代表列，后一个参数代表行）
		hSheet.createFreezePane(0, 1);
		
		return hWorkbook;
	}

	/**
	 * <p>Title: generateTitle</p>
	 * <p>Description: 组装列名</p>
	 * @param exportType	模板编号
	 * @param fieldList		列名集合
	 * @param columnSize	列数
	 * @param hSheet		sheet页
	 * @param hRow			行
	 * @param cellStyle		单元格样式
	 */
	private void generateTitle(Integer exportType, List<Field> fieldList, int columnSize, HSSFSheet hSheet,
			HSSFRow hRow, HSSFCellStyle cellStyle) {
		HSSFCell hCell;
		for(int i = 0; i < columnSize; i++) {
			Field field = fieldList.get(i);
			if(field.isAnnotationPresent(ExcelAnnotation.class)) {
				ExcelAnnotation annotation = field.getAnnotation(ExcelAnnotation.class);
				String columnName = annotation.name().length > exportType ? 
						annotation.name()[exportType] : annotation.name()[0];

				hCell = hRow.createCell(i);
				hCell.setCellValue(columnName);
				hCell.setCellStyle(cellStyle);
				hSheet.setColumnWidth(i, annotation.width());
			}
		}
	}
	
	/**
	 * <p>Title: generateData</p>
	 * <p>Description: 组装Excel数据</p>
	 * @param list			具体数据
	 * @param fieldList		列名
	 * @param columnSize	列数
	 * @param rowIndex		行数计数
	 * @param hSheet		sheet页
	 * @param cellStyle		样式
	 * @throws SecurityException 
	 * @throws NoSuchMethodException 
	 * @return 
	 */
	private int generateData(List<T> list, List<Field> fieldList, int columnSize, 
			int rowIndex, HSSFSheet hSheet, HSSFCellStyle cellStyle) throws NoSuchMethodException, SecurityException {
		HSSFRow hRow;
		HSSFCell hCell;
		for(Object model : list) {
			hRow = hSheet.createRow(rowIndex++);
			Class clazz = model.getClass();
			for(int i = 0; i < columnSize; i++) {
				Field field = fieldList.get(i);
				String methodName = "get" + field.getName().substring(0, 1).toUpperCase() + field.getName().substring(1);
				Method method = clazz.getMethod(methodName);
				try {
					// 获取该字段的注解对象
					Object result = method.invoke(model);
					hCell = hRow.createCell(i);
					if(result != null) {
						if(result.getClass().isAssignableFrom(Date.class)) {
							SimpleDateFormat format = new SimpleDateFormat("yyyy年MM月dd日");
							result = format.format(result);
						}
						hCell.setCellValue(result.toString());
					}else {
						hCell.setCellValue(new HSSFRichTextString(""));
					}
					hCell.setCellStyle(cellStyle);
				} catch (Exception e) {
					e.printStackTrace();
				} 
			}
		}
		return rowIndex;
	}

	/**
	 * <p>Title: getCellStyle</p>
	 * <p>Description: 设置excel表格默认样式</p>
	 * @param hWorkbook		需导出Excel数据
	 * @param fontHeight	字体粗度
	 * @param boldWeight	表格线的粗度
	 * @return
	 */
	private HSSFCellStyle getCellStyle(HSSFWorkbook hWorkbook, short fontHeight, short boldWeight) {
		HSSFCellStyle cellStyle;
		HSSFFont font;
		
		cellStyle = hWorkbook.createCellStyle();
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
		cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
		cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
		cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
		
		font = hWorkbook.createFont();
		font.setFontHeight(fontHeight);
		font.setBoldweight(boldWeight);
		font.setFontName("宋体");
		
		cellStyle.setFont(font);
		cellStyle.setWrapText(true);
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		
		return cellStyle;
	}
	
	/**
	 * <p>Title: writeExcel07S</p>
	 * <p>Description: 创建07版本的表格（xls）</p>
	 * @param list
	 * @param title
	 * @param className
	 * @param exportType
	 * @return
	 * @throws InvocationTargetException 
	 * @throws IllegalAccessException 
	 * @throws SecurityException 
	 * @throws NoSuchMethodException 
	 */
	public SXSSFWorkbook writeExcel07S(List<T> list, String title, Class className, Integer exportType) 
			throws NoSuchMethodException, SecurityException, IllegalAccessException, InvocationTargetException 
			 {
		// 获取属性
		Field[] fields = className.getDeclaredFields();
 		List<Field> fieldList = new ArrayList<Field>();
 		for(Field field : fields) {
 			if(field.isAnnotationPresent(ExcelAnnotation.class))
 				fieldList.add(field);
 		}
 		// 按照id排序
 		Collections.sort(fieldList, new Comparator<Field>() {
			@Override
			public int compare(Field f1, Field f2) {
				return f1.getAnnotation(ExcelAnnotation.class).id() - f2.getAnnotation(ExcelAnnotation.class).id();
			}
 		});
 		
 		int columnSize = fieldList.size(), rowIndex = 0;
		// 创建workbook对象
 		SXSSFWorkbook hWorkbook = new SXSSFWorkbook();
 		// 创建sheet
		Sheet hSheet = hWorkbook.createSheet();
		// 创建标题行（第一行）
		Row hRow = hSheet.createRow(rowIndex++);
		hRow.setHeight((short)300);
		Cell hCell = hRow.createCell(0);
		CellStyle cellStyle = getCellStyle07S(hWorkbook, (short)300, (short)500);
		hCell.setCellStyle(cellStyle);
		hCell.setCellValue(title);
		
		if(getHuoResult(list.isEmpty(), fieldList.isEmpty(), list == null))
			return hWorkbook;
		
		// 创建列名行（第二行）
		hRow = hSheet.createRow(rowIndex++);
		cellStyle = getCellStyle07S(hWorkbook, (short)270, (short)500);
		createTitle07S(exportType, fieldList, columnSize, hSheet, hRow, cellStyle);
		// 创建数据内容
		cellStyle = getCellStyle07S(hWorkbook, (short)220, (short)500);
		generateData07S(list, fieldList, columnSize, rowIndex, hSheet, cellStyle);
		
		/*
		 * 第1个参数：从哪一行开始
	         * 第2个参数：到哪一行结束
	         * 第3个参数：从哪一列开始
	         * 第4个参数：到哪一列结束
		 */
		hSheet.addMergedRegion(new CellRangeAddress(0, 0, 0, columnSize-1));
		//固定表头（第一个参数代表列，第二个参数代表行）
		hSheet.createFreezePane(0, 1);
		
 		return hWorkbook;
	}

	/**
	 * <p>Title: createTitle07S</p>
	 * <p>Description: 生成07表格的列名</p>
	 * @param exportType	模板编号
	 * @param fieldList		列名
	 * @param columnSize 	列数
	 * @param hSheet
	 * @param hRow
	 * @param cellStyle
	 */
	private void createTitle07S(Integer exportType, List<Field> fieldList, int columnSize, Sheet hSheet, Row hRow, CellStyle cellStyle) {
		Cell hCell;
		for(int i = 0; i < columnSize; i++) {
			Field field = fieldList.get(i);
			if(field.isAnnotationPresent(ExcelAnnotation.class)) {
				ExcelAnnotation annotation = field.getAnnotation(ExcelAnnotation.class);
				String columnName = annotation.name().length > exportType ? annotation.name()[exportType] : annotation.name()[0];

				hCell = hRow.createCell(i);
				hCell.setCellValue(columnName);
				hCell.setCellStyle(cellStyle);
				hSheet.setColumnWidth(i, annotation.width());
			}
		}
	}

	/**
	 * <p>Title: generateData07S</p>
	 * <p>Description: </p>
	 * @param list
	 * @param fieldList
	 * @param columnSize
	 * @param rowIndex
	 * @param hSheet
	 * @param cellStyle
	 * @return
	 * @throws SecurityException 
	 * @throws NoSuchMethodException 
	 * @throws InvocationTargetException 
	 * @throws IllegalAccessException 
	 */
	private int generateData07S(List<T> list, List<Field> fieldList, int columnSize, int rowIndex, Sheet hSheet,
			CellStyle cellStyle) throws NoSuchMethodException, SecurityException, IllegalAccessException, InvocationTargetException {
		Row hRow;
        Cell hCell;
        for (Object model : list) {
            hRow = hSheet.createRow(rowIndex++);
            // 获取该类 并获取自身方法
            Class clazz = model.getClass();
            for (int i = 0; i < columnSize; i++) {
                Field field = fieldList.get(i);
                String methodName = "get" + field.getName().substring(0, 1).toUpperCase()
                + field.getName().substring(1);
                Method method = clazz.getMethod(methodName);
                try {
                    // 获取该字段的注解对象
                    Object result = method.invoke(model);
                    hCell = hRow.createCell((short) i);
                    if (result != null) {
                        if (result.getClass().isAssignableFrom(Date.class)) {
                            SimpleDateFormat format = new SimpleDateFormat("yyyy年MM月dd日");
                            result = format.format(result);
                        }
                        hCell.setCellValue(new XSSFRichTextString(result.toString()));
                    } else {
                        hCell.setCellValue(new XSSFRichTextString(""));
                    }
                    hCell.setCellStyle(cellStyle);
                } catch (IllegalArgumentException e) {
                    System.out.println(e.getMessage());
                }
            }
        }
        return rowIndex;
	}

	/**
	 * <p>Title: getCellStyle07S</p>
	 * <p>Description: 设置excel 07表格默认样式</p>
	 * @param hWorkbook
	 * @param fontHeight
	 * @param boldWeight
	 * @return
	 */
	public CellStyle getCellStyle07S(SXSSFWorkbook hWorkbook, short fontHeight, short boldWeight) {
        CellStyle cellStyle;
        Font font;
        
        cellStyle = hWorkbook.createCellStyle();
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
        cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
        cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
        cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
        
        font = hWorkbook.createFont();
        font.setFontHeight(fontHeight);
        font.setBoldweight(boldWeight);
        font.setFontName("宋体");
        
        cellStyle.setFont(font);
        cellStyle.setWrapText(true);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        
        return cellStyle;
    }
	
	/**
	 * <p>Title: getHuoResult</p>
	 * <p>Description: 获取或运算的结果</p>
	 * @param bs
	 * @return
	 */
	private static boolean getHuoResult(Boolean... bs) {
		for(boolean b : bs) {
			if(b) {
				return b;
			}
		}
		return false;
	}
	
}

